package com.example.om_export.app.report.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;



public class ExcelUtil {
    public  static XSSFWorkbook  exportExcel(String title,String jsonTitile,List<Map<String,Object>> dataSet,String pattern){
        //声明一个工作博
//        HSSFWorkbook workBook=new HSSFWorkbook();
        XSSFWorkbook workBook = new XSSFWorkbook();
        //生成一个表格
//        HSSFSheet sheet=workBook.createSheet(title);
        XSSFSheet sheet = workBook.createSheet(title);
        if(title.equals("资产经营人员列表")){
            // 设置列宽
            sheet.setColumnWidth(0, 2800);
            sheet.setColumnWidth(1, 6500);
            sheet.setColumnWidth(2, 5500);
            sheet.setColumnWidth(3, 13500);

        }else{
            //设置表格默认宽度为15个字节
            sheet.setDefaultColumnWidth((short)15);
        }


        //生成一个样式
//        HSSFCellStyle style=workBook.createCellStyle();
        XSSFCellStyle style = workBook.createCellStyle();
        String numberFields="AverageUnitPrice,RentalRate,AnnualRent,RentPrice,danjia,rentPrice,rentPriceXu,RentalRatio,rateYearGrowth,avgRateLastYear,RentalRateLastMonth,avgRateThisYear"
                + ",AvgPriceYearGrowth,AvgPriceLinkGrowth,AvgPriceLastYear,AvgPriceLastMonth";
        String numberFields1="totalAmount,Covered_area,TotalAmount,ThisYearCumulativeRevenue,ThisMonthContractReceivable,ThisMonthActualRentalIncome,AccountsReceivableThisMonth,RealIncomeThisMonth,CumulativeRevenueThisYear,TotalAmount,nianshouru,realRentLastYear,BudgetThisYear,BudgetLastYear,BudgetTargetYear,"
                + "CumulativeRevenueYearGrowth,unitArea,CumulativeRevenueLinkGrowth,LastMonthCumulativeRevenue,LastYearCumulativeRevenue,VacantSpace,EndMonthCanLeasedArea,EndMonthRentalLeasedArea,LeasedArea,available_Lease_Area,underArea,totalInvestment"; //（数值）千位分节符
        String numberFileds2="contractCount,thisMonthSignCount,thisMonthXSignCount,thisMonthOverCount,projectCount";//整数类型字段
        String numberFileds3="managers";//经营人员列表，字符左对齐
        String charFileds="RentalRate";//字符右对齐字段
        //设置样式
        style.setFillBackgroundColor(HSSFColor.WHITE.index);
        style.setFillForegroundColor(HSSFColor.WHITE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //生成一个字体
//        HSSFFont font = workBook.createFont();
        XSSFFont font = workBook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //应用字体到当前样式
        style.setFont(font);


        //生成数字样式
//        HSSFCellStyle numberStyle =workBook.createCellStyle();
        XSSFCellStyle numberStyle = workBook.createCellStyle();
        numberStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        numberStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        numberStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        numberStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        numberStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        numberStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        numberStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        numberStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//        HSSFFont numberFont = workBook.createFont();
        XSSFFont numberFont = workBook.createFont();
        numberFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        numberStyle.setFont(numberFont);
        //数值格式 小数0.00 整数0
//        HSSFDataFormat format = workBook.createDataFormat();
        XSSFDataFormat format = workBook.createDataFormat();
        numberStyle.setDataFormat(format.getFormat("0.00"));

        //生成千位分隔符数字格式 右对齐
//        HSSFCellStyle numberStyle1 =workBook.createCellStyle();
        XSSFCellStyle numberStyle1 = workBook.createCellStyle();
        numberStyle1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        numberStyle1.setFillForegroundColor(HSSFColor.WHITE.index);
        numberStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        numberStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        numberStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        numberStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        numberStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        numberStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//        HSSFFont numberFont1 = workBook.createFont();
        XSSFFont numberFont1 = workBook.createFont();
        numberFont1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        numberStyle1.setFont(numberFont1);

//        HSSFDataFormat format1 = workBook.createDataFormat();
        XSSFDataFormat format1 = workBook.createDataFormat();
        numberStyle1.setDataFormat(format1.getFormat("#,##0.00"));

        //生成整数格式 右对齐
//        HSSFCellStyle numberStyle2 =workBook.createCellStyle();
        XSSFCellStyle numberStyle2 = workBook.createCellStyle();
        numberStyle2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//右对齐
        numberStyle2.setFillForegroundColor(HSSFColor.WHITE.index);
        numberStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        numberStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        numberStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        numberStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        numberStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        numberStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//        HSSFFont numberFont2 = workBook.createFont();
        XSSFFont numberFont2 = workBook.createFont();
        numberFont2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        numberStyle2.setFont(numberFont2);

//        HSSFDataFormat format2 = workBook.createDataFormat();
        XSSFDataFormat format2 = workBook.createDataFormat();
        numberStyle2.setDataFormat(format2.getFormat("0"));

        //生成字符右对样式
//        HSSFCellStyle charStyle =workBook.createCellStyle();
        XSSFCellStyle charStyle = workBook.createCellStyle();
        charStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//右对齐
        charStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        charStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        charStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        charStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        charStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        charStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        charStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//        HSSFFont charFont = workBook.createFont();
        XSSFFont charFont = workBook.createFont();
        charFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        charStyle.setFont(numberFont2);

//        HSSFDataFormat char1 = workBook.createDataFormat();
        XSSFDataFormat char1 = workBook.createDataFormat();
        charStyle.setDataFormat(char1.getFormat("@"));



        // 生成并设置另一个样式

//        HSSFCellStyle style2 = workBook.createCellStyle();
        XSSFCellStyle style2 = workBook.createCellStyle();

        style2.setFillForegroundColor(HSSFColor.WHITE.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style2.setWrapText(true);//自动换行

//        HSSFCellStyle style3 = workBook.createCellStyle();
        XSSFCellStyle style3 = workBook.createCellStyle();
        style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐
        style3.setFillForegroundColor(HSSFColor.WHITE.index);
        style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style3.setWrapText(true);//自动换行
        // 生成另一个字体
//        HSSFFont font2 = workBook.createFont();
        XSSFFont font2 = workBook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        //生成大标题行
//        HSSFCellStyle headStyle = workBook.createCellStyle();
        XSSFCellStyle headStyle = workBook.createCellStyle();
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        HSSFFont headFont=workBook.createFont();
        XSSFFont headFont = workBook.createFont();
        headFont.setFontHeightInPoints((short)30);
        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headStyle.setFont(headFont);
        JSONArray jArr=JSON.parseArray(jsonTitile);
        CellRangeAddress cra=new CellRangeAddress(0,0,0,jArr.size()-1);
        sheet.addMergedRegion(cra);
        Row headRow = sheet.createRow(0);
        Cell cell_1 = headRow.createCell(0);
        cell_1.setCellStyle(headStyle);
        cell_1.setCellValue(title);
        //给标题行添加边框
        int border = 1;
        RegionUtil.setBorderBottom(border,cra, sheet, workBook);
        RegionUtil.setBorderLeft(border,cra, sheet, workBook);
        RegionUtil.setBorderRight(border,cra, sheet, workBook);
        RegionUtil.setBorderTop(border,cra, sheet, workBook);

        //产生表格标题行
//        HSSFRow row = sheet.createRow(1);
        XSSFRow row = sheet.createRow(1);
        for (short i = 0; i < jArr.size(); i++) {
//            HSSFCell cell = row.createCell(i);
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
//            HSSFRichTextString text = new HSSFRichTextString(jArr.getJSONObject(i).getString("filedName"));
            XSSFRichTextString text = new XSSFRichTextString(jArr.getJSONObject(i).getString("filedName"));
            cell.setCellValue(text);
        }

        int index=1;
//        HSSFFont font3 = workBook.createFont();
        XSSFFont font3 = workBook.createFont();
        for(Map<String,Object> data:dataSet){
            index++;
            row=sheet.createRow(index);
            for(int col=0;col<jArr.size();col++){
//                HSSFCell cell=row.createCell(col);
                XSSFCell cell = row.createCell(col);
                Object value=data.get(jArr.getJSONObject(col).getString("field"));
                //判断值的类型后进行强制类型转换
                String textValue = null;
                if (value instanceof Boolean) {
                    boolean bValue = (Boolean) value;
                    textValue = "是";
                    if (!bValue) {
                        textValue ="否";
                    }
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else{
                    if(value==null){value="";}
                    textValue = value.toString();
                }
                if(textValue!=null){
                    //  Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                    ///  Matcher matcher = p.matcher(textValue);
                    boolean is=false;
                    if( numberFileds3.indexOf(jArr.getJSONObject(col).getString("field"))>-1){
                        is=true;
                    }else{
                        //在这个位置将数字中的 "," 去掉 否则后面的正则表达式将不能匹配

                        textValue=textValue.replace(",","");

                        Pattern p = Pattern.compile("^(-?\\d+)(\\.\\d+)?$");
                        Matcher matcher=p.matcher(textValue);
                        //匹配运算只运行一次 提高效率
                        is=matcher.matches();
                    }
                    if( is&& numberFields.indexOf(jArr.getJSONObject(col).getString("field"))>-1){
                        //是数字当作double处理
                        cell.setCellStyle(numberStyle);
                        cell.setCellValue(Double.parseDouble(textValue));
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC );
                    }else if(is && numberFields1.indexOf(jArr.getJSONObject(col).getString("field"))>-1){
                        //是数字(千位分节符) 增加千位分节符样式 ， numberFields1 里面包含的字段都用这个样式处理

                        cell.setCellStyle(numberStyle1);
                        cell.setCellValue(Double.parseDouble(textValue));
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC );
                    }else if(is && numberFileds2.indexOf(jArr.getJSONObject(col).getString("field"))>-1){
                        //是整数 去掉小数点
                        cell.setCellStyle(numberStyle2);
                        cell.setCellValue(Integer.parseInt(textValue));
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC );

                    }else if(charFileds.indexOf(jArr.getJSONObject(col).getString("field"))>-1){
                        //字符右对齐字段
                        cell.setCellStyle(charStyle);
                        cell.setCellValue(textValue);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING );
                    }else if( numberFileds3.indexOf(jArr.getJSONObject(col).getString("field"))>-1){
                        cell.setCellStyle(style3);
//                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        XSSFRichTextString richString = new XSSFRichTextString(textValue);
                        font3.setColor(HSSFColor.BLACK.index);
                        richString.applyFont(font3);
                        cell.setCellValue(richString);
                    }else{
                        cell.setCellStyle(style2);
//                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        XSSFRichTextString richString = new XSSFRichTextString(textValue);
                        font3.setColor(HSSFColor.BLACK.index);
                        richString.applyFont(font3);
                        cell.setCellValue(richString);
                    }
                }

            }
        }
        return workBook;
    }
}
